﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RoadTeam.SysInterface;
using RoadTeam.Model;
using RoadTeam.Enums;
using System.Data;
using RoadTeam.DAL;

namespace RoadTeam.BsSystem.RM
{
   public class RmUnit_Sys : IRmUnit
    {

       public String GetUnitID(String UnitIdentifier)
       {
           SqlWhere where = new SqlWhere();
           where.Info = new TbRmUnit() { UnitIdentifier = UnitIdentifier };
           where.SelectColumn = @" Top 1 UnitID  ";
         DataTable dt=  TbRmUnit.Select(where, GlobalConfig.RtConnectionString);
         if (dt.Rows.Count == 0) return @"-1";
         return dt.Rows[0][0].ToString();
       }

       public DataTable GetUnitTable()
       {
           String StrSql = @"SELECT UnitID,
UnitCode,
UnitName,
UnitFullName,
UnitIdentifier,
TbRmBuilding.OrganID as OrganID
FROM TbRmBuilding 
INNER JOIN TbRmPavilion on TbRmBuilding.BuildingID=TbRmPavilion.BuildingID
INNER JOIN  TbRmUnit on TbRmUnit.PavilionID=TbRmPavilion.PavilionID
WHERE UnitIdentifier IS NULL  AND DoorTypeID Is NOT NULL ";
         DataTable dt=   DbFactory.CreateInstance().CreateDb().ExecuteStrQuery(StrSql, CommandType.Text, GlobalConfig.RtConnectionString);
         return dt;
       }

       public DataTable GetUnitTable(String OrganID)
       {
           String StrSql = @"SELECT TOP 200
UnitID,
DoorTypeID,
UnitIdentifier,
TbRmBuilding.OrganID as OrganID
FROM TbRmBuilding 
INNER JOIN TbRmPavilion on TbRmBuilding.BuildingID=TbRmPavilion.BuildingID
INNER JOIN  TbRmUnit on TbRmUnit.PavilionID=TbRmPavilion.PavilionID
WHERE UnitIdentifier IS NOT NULL AND DoorTypeID Is NOT NULL   AND  OrganID =" + OrganID;
           DataTable dt = DbFactory.CreateInstance().CreateDb().ExecuteStrQuery(StrSql, CommandType.Text, GlobalConfig.RtConnectionString);
           return dt;
       }

       public bool UpdateUnitTable(DataTable dt)
       {
         return  TbRmUnit.Update(dt, GlobalConfig.RtConnectionString);
       }

       public DataTable GetUnitDataByPage(String OrganID, int PageSize,int CurrentPage ,int AllCount)
       {
           String Str = @"SELECT  TbRmUnit.UnitID, [Floor],FloorCode,FloorName,
UnitCode,UnitName,UnitFullName,TbCsDoorType.DoorTypeID,
TbCsDoorType.App_Identifier AS DoorTypeIdentifier,
TbRmUnit.UnitIdentifier as [UnitIdentifier],
TbRmPavilion.PavilionID,PavilionIdentifier,
TbRmPavilion.PavilionCode,TbRmPavilion.PavilionName,
TbRmBuilding.BuildingID,BuildingIdentifier,
TbRmBuilding.BuildingCode,TbRmBuilding.BuildingName,
TbRmBuilding.OrganID,TbSmOrgan.OrganIdentifier
FROM TbRmUnit  
INNER JOIN  TbRmPavilion
on TbRmUnit.PavilionID=TbRmPavilion.PavilionID
INNER JOIN TbRmBuilding 
on TbRmBuilding.BuildingID=TbRmPavilion.BuildingID
INNER join TbSmOrgan on TbRmBuilding.OrganID =TbSmOrgan.OrganID
INNER JOIN TbCsDoorType on TbCsDoorType.DoorTypeID=TbRmUnit.DoorTypeID
WHERE TbRmBuilding.OrganID=" + OrganID + "   ORDER BY TbRmUnit.UnitID";

           
           String StrSql = SQLPage.MakeSQLStringByPage(Str, String.Empty, PageSize, CurrentPage, AllCount);

           DataTable dt = DbFactory.CreateInstance().CreateDb().
        ExecuteStrQuery(StrSql, CommandType.Text, GlobalConfig.RtConnectionString);

           return dt;
       }
       public int GetUnitDataAllPageCount(String OrganID)
       {
           String Str = @"SELECT count (*) as  counts from (
SELECT  TbRmUnit.UnitID, [Floor],FloorCode,FloorName,
UnitCode,UnitName,UnitFullName,TbCsDoorType.DoorTypeID,
TbCsDoorType.App_Identifier AS DoorTypeIdentifier,
TbRmUnit.UnitIdentifier as [UnitIdentifier],
TbRmPavilion.PavilionID,PavilionIdentifier,
TbRmPavilion.PavilionCode,TbRmPavilion.PavilionName,
TbRmBuilding.BuildingID,BuildingIdentifier,
TbRmBuilding.BuildingCode,TbRmBuilding.BuildingName,
TbRmBuilding.OrganID,TbSmOrgan.OrganIdentifier
FROM TbRmUnit  
INNER JOIN  TbRmPavilion
on TbRmUnit.PavilionID=TbRmPavilion.PavilionID
INNER JOIN TbRmBuilding 
on TbRmBuilding.BuildingID=TbRmPavilion.BuildingID
INNER join TbSmOrgan on TbRmBuilding.OrganID =TbSmOrgan.OrganID
INNER JOIN TbCsDoorType on TbCsDoorType.DoorTypeID=TbRmUnit.DoorTypeID
WHERE TbRmBuilding.OrganID=" + OrganID + ") as table1";

        DataTable dt = DbFactory.CreateInstance().CreateDb().
        ExecuteStrQuery(Str, CommandType.Text, GlobalConfig.RtConnectionString);

       return Convert.ToInt32(dt.Rows[0][0]);

       }

       public DataTable GetUnitData(String Where)
       {
           SqlWhere where = new SqlWhere();
           where.StrSqlWhere = @"UnitID in (" + Where + ")";
           where.SelectColumn = @" UnitID,UnitIdentifier ";
           return TbRmUnit.Select(where, GlobalConfig.RtConnectionString);
       }

    }
}
